const DB = require('../utils/useMysql')

/**
 * 获取书签列表
 * @param {string} books_id      书签id
 * @param {number} page          页码
 * @param {number} limit         每页条数
 * @returns
 */
exports.getBooksListSql = async (page, limit, books_id) => {
  const count = await DB.exec({
    sql: `SELECT COUNT(*) as total FROM books WHERE is_show = 1`,
    params: []
  })

  const total = count[0]['total']

  if (total === 0) {
    return { result: [], total }
  }

  const result = books_id
    ? await DB.exec({
        sql: `SELECT 
        book_id, book_url, book_name, book_desc, books.books_id, books.books_desc,
        books.books_name, books.status as books_status, book.status as book_status
        FROM book 
        INNER JOIN books ON book.books_id = books.books_id 
        WHERE books.books_id = ? AND books.is_show = 1 AND book.is_show = 1 ORDER BY books_id DESC LIMIT ? OFFSET ? ;`,
        params: [books_id, limit, page - 1]
      })
    : await DB.exec({
        sql: `SELECT 
        book_id, book_url, book_name, book_desc, books.books_id, books.books_desc,
        books.books_name, books.status as books_status, book.status as book_status
        FROM book 
        INNER JOIN books ON book.books_id = books.books_id 
        WHERE books.is_show = 1 AND book.is_show = 1 ORDER BY books_id DESC LIMIT ? OFFSET ?;`,
        params: [limit, page - 1]
      })

  return { result, total }
}

/**
 * 添加书签列表
 * @param {string} books_id      书签id
 * @param {string} books_name    书签名称
 * @param {string} books_desc    书签描述
 * @param {number} status        书签状态
 * @returns
 */
exports.addBooksListSql = (books_id, books_name, books_desc, status) => {
  return DB.exec({
    sql: `INSERT INTO books (books_id, books_name, books_desc, status, is_show) VALUES  (?, ?, ?, ?, 1)`,
    params: [books_id, books_name, books_desc, status]
  })
}

/**
 * 修改书签列表
 * @param {string} books_id      书签id
 * @param {string} books_name    书签名称
 * @param {string} books_desc    书签描述
 * @param {number} status        书签状态
 * @returns
 */
exports.updateBooksListSql = (books_id, books_name, books_desc, status) => {
  return DB.exec({
    sql: `UPDATE books SET books_name = ?, books_desc = ?, status = ? WHERE books_id = ? AND is_show = 1`,
    params: [books_name, books_desc, books_id, status]
  })
}

/**
 * 删除书签列表
 * @param {string} books_id      书签id
 * @returns
 */
exports.deleteBooksListSql = (books_id) => {
  return DB.exec({
    sql: `UPDATE books SET is_show = 0 WHERE books_id = ?`,
    params: [books_id]
  })
}
